/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package model;


import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author GiK
 */
public class Sklad_drugs {
    


    public static ArrayList<Drug> getDrugByName(String name) throws SQLException
    {
        Database db = new Database();
        ResultSet set = db.getDBInfo(
                "SD.id, D.type, D.name, sell, sellMedium, sellHigh, currentAmount, date, SD.rec_id, skladName, skl",
                "(skladDetailed SD LEFT JOIN drugs D ON SD.id = D.id) ",
                "(CONCAT(D.type,' ',D.name) LIKE '%"+name+"%') " +
                " AND currentAmount > 0 AND D.type !='muud' ORDER BY name DESC ");
        ArrayList<Drug> list = new ArrayList<Drug>();
        while ( set.next())
        {
            Drug drug = new Drug();
            drug.setId(set.getInt(1));
            drug.setType(set.getString(2));
            drug.setName(set.getString(3));
            drug.setSellPrices(set.getDouble(4), set.getDouble(5), set.getDouble(6));
            drug.setAmount(set.getDouble(7));
            drug.setInDate(set.getDate(8));
            drug.setSklad(new Sklad(set.getInt("skl"),set.getString("skladName")));
            list.add(drug);

        }
        db.closeStatement();
        return list;
    }
      public static ArrayList<Drug> getByName(String name) throws SQLException
    {
        Database db = new Database();
        ResultSet set = db.getDBInfo(
                "SD.id, D.type, D.name, sell, sellMedium, sellHigh, currentAmount, date, SD.rec_id, skladName, skl",
                "(skladDetailed SD LEFT JOIN drugs D ON SD.id = D.id) ",
                "(CONCAT(D.type,' ',D.name) LIKE '%"+name+"%') " +
                " AND currentAmount > 0 ORDER BY name DESC ");
        ArrayList<Drug> list = new ArrayList<Drug>();
        while ( set.next())
        {
            Drug drug = new Drug();
            drug.setId(set.getInt(1));
            drug.setType(set.getString(2));
            drug.setName(set.getString(3));
            drug.setSellPrices(set.getDouble(4), set.getDouble(5), set.getDouble(6));
            drug.setAmount(set.getDouble(7));
            drug.setMaxAmount(set.getDouble(7));
            drug.setInDate(set.getDate(8));
            drug.setRecId(set.getInt("rec_id"));
            drug.setSklad(new Sklad(set.getInt("skl"),set.getString("skladName")));
            list.add(drug);

        }
        db.closeStatement();
        return list;
    }

      public static ArrayList<Drug> getByStrictName(String name) throws SQLException
    {
        Database db = new Database();
        ResultSet set = db.getDBInfo(
                "SD.id as id, D.type as type, D.name as name," +
                "sell, sellMedium, sellHigh, SD.currency, " +
                "currentAmount, date, SD.rec_id as rec_id, skladName, skl",
                "(skladDetailed SD LEFT JOIN drugs D ON SD.id = D.id) ",
                "(CONCAT(D.type,' ',D.name) LIKE '"+name+"') " +
                " AND currentAmount > 0 ORDER BY name DESC ");
        ArrayList<Drug> list = new ArrayList<Drug>();
        while ( set.next())
        {
            Drug drug = new Drug();
            drug.setId(set.getInt("id"));
            drug.setType(set.getString("type"));
            drug.setName(set.getString("name"));
            drug.setCurrency(set.getString("currency"));
            drug.setSellPrices(set.getDouble("sell"), set.getDouble("sellMedium"), set.getDouble("sellHigh"));
            drug.setAmount(set.getDouble("currentAmount"));
            drug.setMaxAmount(set.getDouble("currentAmount"));
            drug.setInDate(set.getDate("date"));
            drug.setRecId(set.getInt("rec_id"));
            drug.setSklad(new Sklad(set.getInt("skl"),set.getString("skladName")));
            list.add(drug);

        }
        db.closeStatement();
        return list;
    }
      
     public static ArrayList<String> getAvailDrugNames(String name) throws SQLException
    {
        Database db = new Database();
        ResultSet set = db.getDBInfo(
                "D.name as name, D.type as type",
                "sklad_drugs SD LEFT JOIN drugs D ON SD.id = D.id",
                "(CONCAT(D.type,' ',D.name) LIKE '%"+name+"%') AND type !='muud' " +
                " AND currentAmount > 0 GROUP BY name ORDER BY type,name DESC");
        ArrayList<String> list = new ArrayList<String>();
        String dName ="";
        while ( set.next())
        {
            dName = "";
            if(set.getString("type") != null && !set.getString("type").isEmpty())
              dName += set.getString("type") + " ";
            dName += set.getString("name");
            list.add(dName);
        }
        db.closeStatement();
        return list;
    }
     public static ArrayList<String> getAvailDrugNamesWithType(String name, String type) throws SQLException
    {
        Database db = new Database();
        ResultSet set = db.getDBInfo(
                "D.name, D.type ",
                "sklad_drugs SD LEFT JOIN drugs D ON SD.id = D.id",
                "(CONCAT(D.type,' ',D.name) LIKE '%"+name+"%') " +
                " AND currentAmount > 0  AND type LIKE '%"+type+"%' GROUP BY name ORDER BY type,name DESC");
        ArrayList<String> list = new ArrayList<String>();
        String dName ="";
        while ( set.next())
        {
            dName = "";
            if(set.getString(2) != null && !set.getString(2).isEmpty())
              dName += set.getString(2) + " ";
            dName += set.getString(1);
            list.add(dName);
        }
        db.closeStatement();
        return list;
    }

    public static ArrayList getByNameAndType(String name, String type) throws SQLException {
        Database db = new Database();
        ResultSet set = db.getDBInfo(
                "SD.id, D.type, D.name, sell, sellMedium, sellHigh, currentAmount, date, SD.rec_id, skladName, skl",
                "(skladDetailed SD LEFT JOIN drugs D ON SD.id = D.id) ",
                "(CONCAT(D.type,' ',D.name) LIKE '%"+name+"%') " +
                " AND currentAmount > 0 AND D.type LIKE '%"+type+"%' ORDER BY name DESC ");
        ArrayList<Drug> list = new ArrayList<Drug>();
        while ( set.next())
        {
            Drug drug = new Drug();
            drug.setId(set.getInt(1));
            drug.setType(set.getString(2));
            drug.setName(set.getString(3));
            drug.setSellPrices(set.getDouble(4), set.getDouble(5), set.getDouble(6));
            drug.setAmount(set.getDouble(7));
            drug.setMaxAmount(set.getDouble(7));
            drug.setInDate(set.getDate(8));
            drug.setRecId(set.getInt("rec_id"));
            drug.setSklad(new Sklad(set.getInt("skl"),set.getString("skladName")));
            list.add(drug);

        }
        db.closeStatement();
        return list;
    }

    public static int getByNameTypeAmountdistrIdSkl(String drugName,
            Date skladDate, double skladCurrentAmount, double skladAmount, int distrId, int sklId) throws SQLException {
         Database db = new Database();
         int id=0;
        ResultSet set = db.getDBInfo(
                "SELECT SD.id from sklad_drugs SD JOIN Drugs D ON SD.id = D.id " +
                "WHERE D.name = '"+drugName+"' AND SD.date='"+skladDate+"' AND SD.currentAmount="+skladCurrentAmount+
                " AND incomeAmount="+skladAmount + " AND skl ="+sklId +" AND distrId="+distrId);
        if(set.next())
            id = set.getInt(1);

        db.closeStatement();
        return id;

    }

    public static int updateDrugAmount(int skladRecId, double drugAmount,
                                 double oldAmount)
                                      throws SQLException
    {

        Database db = new Database();
        int res = db.updateDBInfo("sklad_drugs",
                                  "currentAmount = (currentAmount + "+ oldAmount+") - "+drugAmount+"",
                                  " rec_id="+skladRecId);
        db.closeStatement();
        db=null;
        return res;
    }
    public static int updateDrugAmount(int skladRecId, double drugAmount)
                                      throws SQLException
    {

        Database db = new Database();
        int res = db.updateDBInfo("sklad_drugs",
                                  "currentAmount = "+drugAmount+"",
                                  " rec_id="+skladRecId);
        db.closeStatement();
        db=null;
        return res;
    }
    public static int dischargeDrug(String what, String values,
                                    int skladRecId, double amount)
                                                             throws SQLException
    {
       int res=0;
       Database db = new Database();
       int updateRec = db.updateDBInfo("sklad_drugs",
                                   " currentAmount = currentAmount - " + amount,
                                   " rec_id="+skladRecId);
       if(updateRec <=0)
           throw new SQLException("Ошибка базы данных: невозможно выписать " +
                                  "данное количество ("+amount+") " +
                                                "лекар-ва. ");
       ResultSet set = db.insertDBInfo("discharged_drugs", what ,values);
       if(set.next())
            res = set.getInt(1);
       db.closeStatement();
       return res;
    }

    // for install
    public static int recordAsDischargedDrug(String what, String values,
                                    int skladRecId, double amount)
                                                             throws SQLException
    {
       int res=0;
       Database db = new Database();
       int updateRec = db.updateDBInfo("sklad_drugs",
                                   " currentAmount = " + amount,
                                   " rec_id="+skladRecId);
       ResultSet set = db.insertDBInfo("discharged_drugs", what ,values);
       if(set.next())
            res = set.getInt(1);
       db.closeStatement();
       return res;
    }

    public static int add(String what, String values) throws SQLException
    {
        int res=0;
        Database db = new Database();
        ResultSet set = db.insertDBInfo("sklad_drugs", what ,values);
        if(set.next())
            res = set.getInt(1);
        db.closeStatement();
        return res;
    }


}